﻿Option Strict On
Imports System.Data.OleDb
Imports System.IO
Imports ADOX
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Access
Imports OleDbConnection = System.Data.OleDb.OleDbConnection

Module 手集計

    Public Sub T300_E乱数(ETable As String)
        DEL_table("T300_E*")
        'T160_全出荷E集計からT300_E作成
        MySQL = "SELECT
                    " & ETable & ".順位,
                    " & ETable & ".出荷先,
                    " & ETable & ".出荷先 AS 出荷先1,
                    " & ETable & ".乱数,
                    '' AS 判定,
                    NULL AS ランク,
                    '" & ETable & "' AS ETable
                INTO
                    T300_E乱数
                FROM
                    " & ETable & ";"
        SQL実行(MySQL)

        'T300_E作成へT160_全出荷E集計追加（発送先＝‘A’& 発送先 ,乱数=乱数+1）
        MySQL = "INSERT INTO T300_E乱数 ( 順位, 出荷先, 出荷先1, 乱数, 判定, ランク, ETable ) 
                    SELECT " & ETable & ".順位, " & ETable & ".出荷先, 'A' & [出荷先] AS 出荷先1, [乱数]+1 AS 乱数1, 
                        '' AS 判定, '未定' AS ランク, '" & ETable & "' AS ETable
                    FROM " & ETable & ";"
        SQL実行(MySQL)

    End Sub
    Public Sub T300_I乱数(ITable As String)
        DEL_table("T300_I*")
        'T160_全出荷I集計からT300_I作成
        MySQL = "SELECT " & ITable & ".順位, 
                        " & ITable & ".[アイテム], 
                        " & ITable & ".[アイテム] AS アイテム1, 
                        " & ITable & ".乱数, 
                    '' AS I判定, NULL AS ランク, '" & ITable & "' AS ITable
                    INTO T300_I乱数
                    FROM " & ITable & ";"
        SQL実行(MySQL)

        'T300_I作成へT160_全出荷I集計追加（アイテム＝‘A’& アイテム ,乱数=乱数+1）
        MySQL = "INSERT INTO T300_I乱数 ( 順位, アイテム, アイテム1, 乱数, I判定, ランク, ITable ) 
                    SELECT " & ITable & ".順位, " & ITable & ".[アイテム], 'A' & [アイテム] AS アイテム1, [乱数]+1 AS 乱数1, 
                        '' AS I判定, '未定' AS ランク, '" & ITable & "' AS ITable 
                    FROM " & ITable & ";"
        SQL実行(MySQL)

    End Sub
    Public Sub T350_計算対象1(E採用判定値 As Double)
        ' ※環境に合わせて接続文字列を変更してください
        ' 例: Access 2007以降 (.accdb) の場合
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";"

        ' 実行するSELECT文
        ' (VB.NET 14以降の複数行文字列リテラルを使用しています)
        Dim MYSQL As String = "SELECT
            T300_E乱数.ETable,
            T300_E乱数.乱数,
            T200.行連番,
            T200.[アイテム],
            T300_E乱数.出荷先,
            T300_E乱数.出荷先1,
            T200.G行,
            T200.Gバ,
            T200.Gケ,
            T200.GPL,
            T200.G容,
            T200.G重
        FROM
            T300_E乱数
            LEFT JOIN T200 ON T300_E乱数.出荷先 = T200.出荷先
        WHERE
            (((T300_E乱数.乱数) <= " & E採用判定値 & "));"

        ' クエリを作成するためのDDL文 (CREATE VIEW)
        Dim createQuerySQL As String = "CREATE VIEW Q350_計算対象1 AS " & MYSQL

        ' 既存のクエリを削除するためのDDL文 (DROP VIEW)
        Dim dropQuerySQL As String = "DROP VIEW Q350_計算対象1;"

        Using connection As New OleDbConnection(connectionString)
            Try
                connection.Open()

                ' 1. 既に同名のクエリ(ビュー)が存在する場合は削除する
                Try
                    Using dropCommand As New OleDbCommand(dropQuerySQL, connection)
                        dropCommand.ExecuteNonQuery()
                    End Using
                Catch ex As OleDbException
                    ' クエリが存在しない場合のエラーは無視して次に進む
                End Try

                ' 2. 新規でクエリ(ビュー)を作成する
                Using createCommand As New OleDbCommand(createQuerySQL, connection)
                    createCommand.ExecuteNonQuery()
                End Using

                Console.WriteLine("クエリ「Q350_計算対象1」が正常に作成されました。")

            Catch ex As Exception
                ' 接続やその他のエラー処理
                Console.WriteLine("エラーが発生しました: " & ex.Message)
            End Try
        End Using
    End Sub
    Public Sub T350_計算対象2(I採用判定値 As Double)
        DEL_table("T350*")
        MySQL = "SELECT
                        Q350_計算対象1.行連番,
                        Q350_計算対象1.ETable,
                        T300_I乱数.ITable,
                        Q350_計算対象1.出荷先1 AS 出荷先,
                        T300_I乱数.[アイテム1] AS アイテム,
                        Q350_計算対象1.G行 AS 出庫回数,
                        Q350_計算対象1.Gバ AS バラ,
                        Q350_計算対象1.Gケ AS ケース,
                        Q350_計算対象1.GPL AS PL,
                        Q350_計算対象1.G容 AS 容積,
                        Q350_計算対象1.G重 AS 重量
                    INTO
                        T350_計算対象
                    FROM
                        T300_I乱数
                        LEFT JOIN Q350_計算対象1 ON T300_I乱数.[アイテム] = Q350_計算対象1.[アイテム]
                    WHERE
                        (
                            ((Q350_計算対象1.出荷先) IS NOT NULL)
                            AND ((T300_I乱数.乱数) <= " & I採用判定値 & ")
                        );"
        SQL実行(MySQL)

    End Sub

    Public Sub T350_計算対象作成()
        DEL_table("T780*")
        DEL_table("T800*")
        'T780_採用出荷先作成(T200からT300_E乱数.判定) = 'Yes'よりデータ抽出)
        MySQL = "SELECT
                        T200.行連番,
                        T300_E乱数.出荷先1,
                        T200.アイテム,
                        T200.G行,
                        T200.Gバ,
                        T200.Gケ,
                        T200.GPL,
                        T200.G容,
                        T200.G重
                    INTO
                        T780_採用出荷先
                    FROM
                        T300_E乱数
                        INNER JOIN T200 ON T300_E乱数.出荷先 = T200.出荷先
                    WHERE
                        (((T300_E乱数.判定) = 'Yes'));"
        SQL実行(MySQL)

        'T350_計算対象作成（T780_採用出荷先からT300_I乱数.I判定) = 'Yes'のデータを抽出）
        MySQL = "SELECT
                        T780_採用出荷先.行連番,
                        T780_採用出荷先.出荷先1 as 出荷先,
                        T300_I乱数.[アイテム1] as アイテム,
                        T780_採用出荷先.G行,
                        T780_採用出荷先.Gバ,
                        T780_採用出荷先.Gケ,
                        T780_採用出荷先.GPL,
                        T780_採用出荷先.G容,
                        T780_採用出荷先.G重
                    INTO
                        T350_計算対象
                    FROM
                        T300_I乱数
                        LEFT JOIN T780_採用出荷先 ON T300_I乱数.[アイテム] = T780_採用出荷先.[アイテム]
                    WHERE
                        (((T300_I乱数.I判定) = 'Yes'));"
        SQL実行(MySQL)
    End Sub
    Public Sub T360_全出荷E集計()
        ' ===== 1. T360* テーブルを削除 =====
        DEL_table("T360_全出荷E*")

        ' ===== 2. T360_全出荷E集計 を 出荷先 × PL合計 で作成 =====
        MySQL = "SELECT 出荷先, Sum(PL) AS PL合計 INTO [T360_全出荷E集計] " &
                "FROM T350_計算対象 " &
                "GROUP BY 出荷先"
        SQL実行(MySQL)

        ' ===== 3. PLEランク 列を追加 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Using addCmd As New OleDbCommand(
                    "ALTER TABLE [T360_全出荷E集計] ADD COLUMN [PLEランク] TEXT(10)", cn)
                    addCmd.ExecuteNonQuery()
                End Using
            End Using
        Catch
            ' 列が既に存在する場合は無視
        End Try

        ' ===== 4. PL合計 を降順に読み込み累積比率でランク付け =====
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";"

        Dim totalPL As Double = 0
        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()
                Using cmd As New OleDbCommand(
                    "SELECT Sum(PL合計) FROM [T360_全出荷E集計]", cn)
                    Dim result As Object = cmd.ExecuteScalar()
                    If result IsNot Nothing AndAlso Not IsDBNull(result) Then
                        totalPL = Convert.ToDouble(result)
                    End If
                End Using
            End Using
        Catch ex As Exception
            MsgBox("T360_全出荷E集計: PL合計取得エラー: " & ex.Message)
            Return
        End Try

        If totalPL = 0 Then
            MsgBox("T360_全出荷E集計: T360_全出荷E集計 の PL合計が 0 です。処理を中止します。")
            Return
        End If

        Dim th_A1 As Double = EIQ_A1ランク
        Dim th_A2 As Double = EIQ_A1ランク + EIQ_A2ランク
        Dim th_B As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク
        Dim th_C As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク + EIQ_Cランク

        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()

                Dim 累積PL As Double = 0

                Using selCmd As New OleDbCommand(
                    "SELECT 出荷先, PL合計 FROM [T360_全出荷E集計] ORDER BY PL合計 DESC", cn)
                    Using rdr As OleDbDataReader = selCmd.ExecuteReader()

                        Dim updCmd As New OleDbCommand(
                            "UPDATE [T360_全出荷E集計] SET [PLEランク] = ? WHERE [出荷先] = ?", cn)
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 10))
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 20))

                        While rdr.Read()
                            Dim dest As String = rdr("出荷先").ToString()
                            Dim PL As Double = If(IsDBNull(rdr("PL合計")), 0, Convert.ToDouble(rdr("PL合計")))

                            累積PL += PL
                            Dim 累積比率 As Double = (累積PL / totalPL) * 100.0

                            Dim rank As String
                            If 累積比率 <= th_A1 Then
                                rank = "PLE_A1"
                            ElseIf 累積比率 <= th_A2 Then
                                rank = "PLE_A2"
                            ElseIf 累積比率 <= th_B Then
                                rank = "PLE_B"
                            ElseIf 累積比率 <= th_C Then
                                rank = "PLE_C"
                            Else
                                rank = "PLE_D"
                            End If

                            updCmd.Parameters(0).Value = rank
                            updCmd.Parameters(1).Value = dest
                            updCmd.ExecuteNonQuery()
                        End While

                        updCmd.Dispose()
                    End Using
                End Using

                cn.Close()
            End Using
        Catch ex As Exception
            MsgBox("T360_全出荷E集計: ランク更新エラー: " & ex.Message)
            Return
        End Try

        ' ===== 5. T350_計算対象 の PLEランク 列に T360_全出荷E集計 のランクを反映 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Try
                    Using addCmd As New OleDbCommand(
                        "ALTER TABLE [T350_計算対象] ADD COLUMN [PLEランク] TEXT(10)", cn)
                        addCmd.ExecuteNonQuery()
                    End Using
                Catch
                    ' 既存の場合は無視
                End Try
            End Using
        Catch
        End Try

        MySQL = "UPDATE [T350_計算対象] " &
                "INNER JOIN [T360_全出荷E集計] ON [T350_計算対象].出荷先 = [T360_全出荷E集計].出荷先 " &
                "SET [T350_計算対象].[PLEランク] = [T360_全出荷E集計].[PLEランク];"
        SQL実行(MySQL)

    End Sub

    Public Sub T360_全出荷I集計()

        ' ===== 1. T360* テーブルを削除 =====
        DEL_table("T360_全出荷I*")
        ' ===== 2. T360_全出荷I集計 を アイテム × PL合計 で作成 =====
        MySQL = "SELECT アイテム, Sum(PL) AS PL合計 INTO [T360_全出荷I集計] " &
                "FROM T350_計算対象 " &
                "GROUP BY アイテム"
        SQL実行(MySQL)

        ' ===== 3. PLIランク 列を追加 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Using addCmd As New OleDbCommand(
                    "ALTER TABLE [T360_全出荷I集計] ADD COLUMN [PLIランク] TEXT(10)", cn)
                    addCmd.ExecuteNonQuery()
                End Using
            End Using
        Catch
            ' 列が既に存在する場合は無視
        End Try

        ' ===== 4. PL合計 を降順に読み込み累積比率でランク付け =====
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";"

        Dim totalPL As Double = 0
        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()
                Using cmd As New OleDbCommand(
                    "SELECT Sum(PL合計) FROM [T360_全出荷I集計]", cn)
                    Dim result As Object = cmd.ExecuteScalar()
                    If result IsNot Nothing AndAlso Not IsDBNull(result) Then
                        totalPL = Convert.ToDouble(result)
                    End If
                End Using
            End Using
        Catch ex As Exception
            MsgBox("T360_全出荷I集計: PL合計取得エラー: " & ex.Message)
            Return
        End Try

        If totalPL = 0 Then
            MsgBox("T360_全出荷I集計: T360_全出荷I集計 の PL合計が 0 です。処理を中止します。")
            Return
        End If

        Dim th_A1 As Double = EIQ_A1ランク
        Dim th_A2 As Double = EIQ_A1ランク + EIQ_A2ランク
        Dim th_B As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク
        Dim th_C As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク + EIQ_Cランク

        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()

                Dim 累積PL As Double = 0

                Using selCmd As New OleDbCommand(
                    "SELECT アイテム, PL合計 FROM [T360_全出荷I集計] ORDER BY PL合計 DESC", cn)
                    Using rdr As OleDbDataReader = selCmd.ExecuteReader()

                        Dim updCmd As New OleDbCommand(
                            "UPDATE [T360_全出荷I集計] SET [PLIランク] = ? WHERE [アイテム] = ?", cn)
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 10))
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 20))

                        While rdr.Read()
                            Dim item As String = rdr("アイテム").ToString()
                            Dim PL As Double = If(IsDBNull(rdr("PL合計")), 0, Convert.ToDouble(rdr("PL合計")))

                            累積PL += PL
                            Dim 累積比率 As Double = (累積PL / totalPL) * 100.0

                            Dim rank As String
                            If 累積比率 <= th_A1 Then
                                rank = "PLI_A1"
                            ElseIf 累積比率 <= th_A2 Then
                                rank = "PLI_A2"
                            ElseIf 累積比率 <= th_B Then
                                rank = "PLI_B"
                            ElseIf 累積比率 <= th_C Then
                                rank = "PLI_C"
                            Else
                                rank = "PLI_D"
                            End If

                            updCmd.Parameters(0).Value = rank
                            updCmd.Parameters(1).Value = item
                            updCmd.ExecuteNonQuery()
                        End While

                        updCmd.Dispose()
                    End Using
                End Using

                cn.Close()
            End Using
        Catch ex As Exception
            MsgBox("T360_全出荷I集計: ランク更新エラー: " & ex.Message)
            Return
        End Try

        ' ===== 5. T350_計算対象 の PLIランク 列に T360_全出荷I集計 のランクを反映 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Try
                    Using addCmd As New OleDbCommand(
                        "ALTER TABLE [T350_計算対象] ADD COLUMN [PLIランク] TEXT(10)", cn)
                        addCmd.ExecuteNonQuery()
                    End Using
                Catch
                    ' 既存の場合は無視
                End Try
            End Using
        Catch
        End Try

        MySQL = "UPDATE [T350_計算対象] " &
                "INNER JOIN [T360_全出荷I集計] ON [T350_計算対象].アイテム = [T360_全出荷I集計].アイテム " &
                "SET [T350_計算対象].[PLIランク] = [T360_全出荷I集計].[PLIランク];"
        SQL実行(MySQL)
    End Sub

    Public Sub T400_クロス集計()
        DEL_table("T400*")
        ' T350_計算対象 を元に、GPLEランク × GPLIランク のクロス集計を行い、T400_クロス集計 に出力します。
        MySQL = "SELECT
                        T350_計算対象.PLEランク,
                        T350_計算対象.PLIランク,
                        Count(T350_計算対象.行連番) AS 行数,
                        Sum(T350_計算対象.出庫回数) AS 出庫回数,
                        Sum(T350_計算対象.[バラ]) AS バラ,
                        Sum(T350_計算対象.[ケース]) AS ケース,
                        Sum(T350_計算対象.PL) AS PL,
                        Sum(T350_計算対象.容積) AS 容積,
                        Sum(T350_計算対象.重量) AS 重量
                    INTO
                        T400_クロス集計
                    FROM
                        T350_計算対象
                    GROUP BY
                        T350_計算対象.PLEランク,
                        T350_計算対象.PLIランク;"
        SQL実行(MySQL)
    End Sub
    Public Sub T500_データモデル更新改(進行連番 As Integer)



        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";"

        ' T350_計算対象 から 進行連番 を取得（存在しない場合はK1の進行連番を使用）
        Dim モデル連番値 As Integer = 進行連番

        If モデル連番値 = 0 Then
            MsgBox("進行連番が設定されていません。", MsgBoxStyle.Exclamation)
            Return
        End If

        ' ランク名 → 列インデックス（1-5）
        Dim eRankMap As New Dictionary(Of String, Integer) From {
            {"PLE_A1", 1}, {"PLE_A2", 2}, {"PLE_B", 3}, {"PLE_C", 4}, {"PLE_D", 5}
        }
        Dim iRankMap As New Dictionary(Of String, Integer) From {
            {"PLI_A1", 1}, {"PLI_A2", 2}, {"PLI_B", 3}, {"PLI_C", 4}, {"PLI_D", 5}
        }

        ' 集計値格納配列
        Dim eCount(5) As Double
        Dim iCount(5) As Double
        Dim 行数M(25) As Double
        Dim バラ数M(25) As Double
        Dim ケースM(25) As Double
        Dim PLM(25) As Double
        Dim 容積M(25) As Double
        Dim 重量M(25) As Double
        Dim 出荷先数 As Double = 0
        Dim アイテム数 As Double = 0
        Dim 総行数 As Double = 0
        Dim 総バラ数 As Double = 0

        ' ===== データ取得 =====
        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()

                ' T360_全出荷E集計 から出荷先数を取得
                Using cmd As New OleDbCommand("SELECT Count(*) FROM [T360_全出荷E集計]", cn)
                    Dim r As Object = cmd.ExecuteScalar()
                    If r IsNot Nothing AndAlso Not IsDBNull(r) Then 出荷先数 = Convert.ToDouble(r)
                End Using

                ' T360_全出荷I集計 からアイテム数を取得
                Using cmd As New OleDbCommand("SELECT Count(*) FROM [T360_全出荷I集計]", cn)
                    Dim r As Object = cmd.ExecuteScalar()
                    If r IsNot Nothing AndAlso Not IsDBNull(r) Then アイテム数 = Convert.ToDouble(r)
                End Using

                ' T350_計算対象 から総行数・総バラ数を取得
                Using cmd As New OleDbCommand("SELECT Count(行連番), Sum(バラ) FROM [T350_計算対象]", cn)
                    Using rdr As OleDbDataReader = cmd.ExecuteReader()
                        If rdr.Read() Then
                            If Not IsDBNull(rdr(0)) Then 総行数 = Convert.ToDouble(rdr(0))
                            If Not IsDBNull(rdr(1)) Then 総バラ数 = Convert.ToDouble(rdr(1))
                        End If
                    End Using
                End Using

                ' T360_全出荷E集計 から PLEランク別 出荷先数を取得
                Using cmd As New OleDbCommand(
                    "SELECT PLEランク, Count(出荷先) FROM [T360_全出荷E集計] GROUP BY PLEランク", cn)
                    Using rdr As OleDbDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            Dim rnk As String = If(IsDBNull(rdr(0)), "", rdr(0).ToString())
                            Dim cnt As Double = If(IsDBNull(rdr(1)), 0, Convert.ToDouble(rdr(1)))
                            If eRankMap.ContainsKey(rnk) Then eCount(eRankMap(rnk)) = cnt
                        End While
                    End Using
                End Using

                ' T360_全出荷I集計 から PLIランク別 アイテム数を取得
                Using cmd As New OleDbCommand(
                    "SELECT PLIランク, Count(アイテム) FROM [T360_全出荷I集計] GROUP BY PLIランク", cn)
                    Using rdr As OleDbDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            Dim rnk As String = If(IsDBNull(rdr(0)), "", rdr(0).ToString())
                            Dim cnt As Double = If(IsDBNull(rdr(1)), 0, Convert.ToDouble(rdr(1)))
                            If iRankMap.ContainsKey(rnk) Then iCount(iRankMap(rnk)) = cnt
                        End While
                    End Using
                End Using

                ' T400_クロス集計 から PLEランク×PLIランク のクロス集計を取得
                Using cmd As New OleDbCommand(
                    "SELECT PLEランク, PLIランク, 行数, 出庫回数, バラ, ケース, PL, 容積, 重量 " &
                    "FROM [T400_クロス集計]", cn)
                    Using rdr As OleDbDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            Dim eRnk As String = If(IsDBNull(rdr("PLEランク")), "", rdr("PLEランク").ToString())
                            Dim iRnk As String = If(IsDBNull(rdr("PLIランク")), "", rdr("PLIランク").ToString())
                            If Not (eRankMap.ContainsKey(eRnk) AndAlso iRankMap.ContainsKey(iRnk)) Then Continue While
                            Dim idx As Integer = (eRankMap(eRnk) - 1) * 5 + iRankMap(iRnk)
                            行数M(idx) = If(IsDBNull(rdr("行数")), 0, Convert.ToDouble(rdr("行数")))
                            バラ数M(idx) = If(IsDBNull(rdr("バラ")), 0, Convert.ToDouble(rdr("バラ")))
                            ケースM(idx) = If(IsDBNull(rdr("ケース")), 0, Convert.ToDouble(rdr("ケース")))
                            PLM(idx) = If(IsDBNull(rdr("PL")), 0, Convert.ToDouble(rdr("PL")))
                            容積M(idx) = If(IsDBNull(rdr("容積")), 0, Convert.ToDouble(rdr("容積")))
                            重量M(idx) = If(IsDBNull(rdr("重量")), 0, Convert.ToDouble(rdr("重量")))
                        End While
                    End Using
                End Using

                cn.Close()
            End Using
        Catch ex As Exception
            MsgBox("T500_データモデル更新改: データ取得エラー: " & ex.Message)
            Return
        End Try

        ' ===== UPDATE を分割して実行（フィールド数上限回避） =====
        Dim whr As String = " WHERE [モデル連番]=" & モデル連番値

        Try
            ' --- (1) 基本集計 + E1-E5 + I1-I5 ---
            MySQL = "UPDATE [T500_データモデル] SET " &
                    "出荷先数=" & 出荷先数 & ", アイテム数=" & アイテム数 & ", " &
                    "バラ数量=" & 総バラ数 & ", 行数=" & 総行数 & ", " &
                    "E1=" & eCount(1) & ", E2=" & eCount(2) & ", E3=" & eCount(3) & ", E4=" & eCount(4) & ", E5=" & eCount(5) & ", " &
                    "I1=" & iCount(1) & ", I2=" & iCount(2) & ", I3=" & iCount(3) & ", I4=" & iCount(4) & ", I5=" & iCount(5) &
                    whr
            SQL実行(MySQL)

            ' --- (2) 行数01-25 ---
            Dim sb As New System.Text.StringBuilder()
            sb.Append("UPDATE [T500_データモデル] SET ")
            For idx As Integer = 1 To 25
                If idx > 1 Then sb.Append(", ")
                sb.Append("行数" & idx.ToString("00") & "=" & 行数M(idx))
            Next
            sb.Append(whr)
            SQL実行(sb.ToString())

            ' --- (3) バラ数01-25 ---
            sb.Clear()
            sb.Append("UPDATE [T500_データモデル] SET ")
            For idx As Integer = 1 To 25
                If idx > 1 Then sb.Append(", ")
                sb.Append("バラ数" & idx.ToString("00") & "=" & バラ数M(idx))
            Next
            sb.Append(whr)
            SQL実行(sb.ToString())

            ' --- (4) ケース01-25 ---
            sb.Clear()
            sb.Append("UPDATE [T500_データモデル] SET ")
            For idx As Integer = 1 To 25
                If idx > 1 Then sb.Append(", ")
                sb.Append("ケース" & idx.ToString("00") & "=" & ケースM(idx))
            Next
            sb.Append(whr)
            SQL実行(sb.ToString())

            ' --- (5) PL01-25 ---
            sb.Clear()
            sb.Append("UPDATE [T500_データモデル] SET ")
            For idx As Integer = 1 To 25
                If idx > 1 Then sb.Append(", ")
                sb.Append("PL" & idx.ToString("00") & "=" & PLM(idx))
            Next
            sb.Append(whr)
            SQL実行(sb.ToString())

            ' --- (6) 容積01-25 ---
            sb.Clear()
            sb.Append("UPDATE [T500_データモデル] SET ")
            For idx As Integer = 1 To 25
                If idx > 1 Then sb.Append(", ")
                sb.Append("容積" & idx.ToString("00") & "=" & 容積M(idx))
            Next
            sb.Append(whr)
            SQL実行(sb.ToString())

            ' --- (7) 重量01-25 ---
            sb.Clear()
            sb.Append("UPDATE [T500_データモデル] SET ")
            For idx As Integer = 1 To 25
                If idx > 1 Then sb.Append(", ")
                sb.Append("重量" & idx.ToString("00") & "=" & 重量M(idx))
            Next
            sb.Append(whr)
            SQL実行(sb.ToString())

        Catch ex As Exception
            MsgBox("T500_データモデル更新改: UPDATE エラー: " & ex.Message)
        End Try

    End Sub
End Module
